﻿using System;
using System.Collections.Generic;
using System.Linq;

namespace Netnr.DataKit.DK
{
    /// <summary>
    /// 实现接口
    /// </summary>
    public class DataKitMySQL : IDataKit
    {
        /// <summary>
        /// 获取数据库名称
        /// </summary>
        public static string DataBaseName
        {
            get
            {
                return DB.MySQLDB.connectionString.Split(';').ToList().FirstOrDefault(x => x.StartsWith("database=", StringComparison.OrdinalIgnoreCase)).Substring(9).Replace("'", "");
            }
        }

        /// <summary>
        /// 获取所有表信息的SQL脚本
        /// </summary>
        public string GetTableSQL = @"
                                        SELECT
                                            table_name AS TableName,
                                            table_comment AS TableComment
                                        FROM
                                            information_schema.tables
                                        WHERE
                                            table_schema = '@DataBaseName@' 
                                        ORDER BY table_name
                                        ".Replace("@DataBaseName@", DataBaseName);

        /// <summary>
        /// 获取所有列信息的SQL脚本
        /// </summary>
        public string GetColumnSQL = @" 
                                        SELECT
                                            T.table_name AS TableName,
                                            T.table_comment AS TableComment,
                                            C.column_name AS FieldName,
                                            C.column_type AS DataTypeLength,
                                            C.data_type AS DataType,
                                            CASE
                                            WHEN C.character_maximum_length IS NOT NULL THEN C.character_maximum_length
                                            WHEN C.numeric_precision IS NOT NULL THEN C.numeric_precision
                                            ELSE NULL
                                            end AS DataLength,
                                            C.numeric_scale AS DataScale,
                                            C.ordinal_position AS FieldOrder,
                                            CASE
                                            WHEN (
                                                SELECT
                                                Count(1)
                                                FROM
                                                information_schema.key_column_usage
                                                WHERE
                                                table_schema = T.table_schema
                                                AND table_name = T.table_name
                                                AND column_name = C.column_name
                                                LIMIT
                                                0, 1
                                            ) = 0 THEN ''
                                            ELSE 'YES'
                                            end AS PrimaryKey,
                                            CASE
                                            WHEN C.EXTRA = 'auto_increment' THEN 'YES'
                                            ELSE ''
                                            END AS AutoAdd,
                                            CASE
                                            WHEN C.is_nullable = 'YES' THEN ''
                                            ELSE 'YES'
                                            end AS NotNull,
                                            C.column_default AS DefaultValue,
                                            C.column_comment AS FieldComment
                                        FROM
                                            information_schema.columns C
                                            LEFT JOIN information_schema.tables T ON C.table_schema = T.table_schema
                                            AND C.table_name = T.table_name
                                        WHERE
                                            T.table_schema = '@DataBaseName@'
                                            AND 1 = 1 @SqlWhere@
                                        ORDER BY
                                            T.table_name,
                                            C.ordinal_position
                                      ".Replace("@DataBaseName@", DataBaseName);

        /// <summary>
        /// 设置表注释的SQL脚本
        /// </summary>
        public string SetTableCommentSQL = "ALTER TABLE `@DataTableName@` COMMENT '@Comment@'";

        /// <summary>
        /// 设置列注释的SQL脚本
        /// </summary>
        public string SetColumnCommentSQL = "ALTER TABLE `@DataTableName@` MODIFY COLUMN `@DataColumnName@` INT COMMENT '@Comment@'";

        /// <summary>
        /// 获取所有列
        /// </summary>
        /// <param name="listTableName">表名</param>
        /// <returns></returns>
        public List<Model.DkTableColumn> GetColumn(List<string> listTableName = null)
        {
            var whereSql = string.Empty;

            if (listTableName?.Count > 0)
            {
                listTableName.ForEach(x => x = x.Replace("'", ""));

                whereSql = "AND T.table_name in ('" + string.Join("','", listTableName) + "')";
            }

            var sql = GetColumnSQL.Replace("@SqlWhere@", whereSql);
            var ds = new DB.MySQLDB().Query(sql);

            var list = ds.Tables[0].ToModel<Model.DkTableColumn>();

            return list;
        }

        /// <summary>
        /// 获取所有表
        /// </summary>
        /// <returns></returns>
        public List<Model.DkTableName> GetTable()
        {
            var ds = new DB.MySQLDB().Query(GetTableSQL);

            var list = ds.Tables[0].ToModel<Model.DkTableName>();

            return list;
        }

        /// <summary>
        /// 设置表注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="TableComment">表注释</param>
        /// <returns></returns>
        public bool SetTableComment(string TableName, string TableComment)
        {
            var sql = SetTableCommentSQL.Replace("@DataTableName@", TableName.Replace("`", "``")).Replace("@Comment@", TableComment.Replace("'", "''"));
            new DB.MySQLDB().ExecuteSql(sql);
            return true;
        }

        /// <summary>
        /// 设置列注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FieldName">列名</param>
        /// <param name="FieldComment">列注释</param>
        /// <returns></returns>
        public bool SetColumnComment(string TableName, string FieldName, string FieldComment)
        {
            var sql = SetColumnCommentSQL.Replace("@DataTableName@", TableName.Replace("`", "``")).Replace("@DataColumnName@", FieldName.Replace("`", "``")).Replace("@Comment@", FieldComment.Replace("'", "''"));
            new DB.MySQLDB().ExecuteSql(sql);
            return true;
        }
    }
}
